/* Program name: auto_debt.sas;
* Objective: create an estimate of vehicle-related debt and student loan debt.;
* This code requires the clo variable, which is created in clean_tradeline04.sas, so cannot 
  be run on the raw tradeline files;
* Note: HARDCODED DATES BELOW;
*/


options mprint;
libname in './';
libname scorein './';
%include "/data/tu_formats.sas";
%include "/data/06macros.sas";




%macro runme;
data temp (keep =  teditseq terms hicredit acctype loantypt loantype acctbal ecoa crdtlim cll auto_: mortgage_: dtveri student_: ccard_: cctot_: install_: lcred_: unsec_: secgoods_: lease_:  dtclose dtopen paypat mop permid rename=(dtclose=dtclo));
  set in.cleantrade&sufx.;

  if clo eq 1 or dispcd in ('IA','INA','FTS','FTB','ETS','ETB','ETI') then coo = 1;
  else coo = 0;

  if dtclose gt 0 or coo eq 1 then cll = 1;
  else if acctype in ('I', 'M') and acctbal le 0 and mop eq 1 then cll = 1;
  else cll = 0;

  if acctbal lt 0 then acctbal = .;


/************************ADDED CODE 5/2019*****************/


/* installment loans */
  if loantypt in('IS') then do;
     loantype = "INSTALL"; 
     install_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     install_hist_ontime = paymop1_24;
     install_hist_3060 = pay3059_24;
     install_hist_6090 = pay6089_24;
     install_hist_90120 = pay90119_24;
     install_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then install_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge &nn2. then do;
	      if acctbal eq 0 then install_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(install);
	end;
     end;
  end;

/* line of credit */
  if loantypt in('LC') then do;
     loantype = "LCRED"; 
     lcred_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     lcred_hist_ontime = paymop1_24;
     lcred_hist_3060 = pay3059_24;
     lcred_hist_6090 = pay6089_24;
     lcred_hist_90120 = pay90119_24;
     lcred_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then lcred_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge &nn2. then do;
	      if acctbal eq 0 then lcred_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(lcred);
	end;
     end;
  end;


/* Unsecured */
  if loantypt in('US') then do;
     loantype = "UNSEC"; 
     unsec_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     unsec_hist_ontime = paymop1_24;
     unsec_hist_3060 = pay3059_24;
     unsec_hist_6090 = pay6089_24;
     unsec_hist_90120 = pay90119_24;
     unsec_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then unsec_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge &nn2. then do;
	      if acctbal eq 0 then unsec_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(unsec);
	end;
     end;
  end;

/* Household goods secured & collateral */
  if loantypt in('SO','SH') then do;
     loantype = "SECGOODS"; 
     secgoods_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     secgoods_hist_ontime = paymop1_24;
     secgoods_hist_3060 = pay3059_24;
     secgoods_hist_6090 = pay6089_24;
     secgoods_hist_90120 = pay90119_24;
     secgoods_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then secgoods_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge &nn2. then do;
	      if acctbal eq 0 then secgoods_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(secgoods);
	end;
     end;
  end;


/* lease */
  if loantypt in('LE') then do;
     loantype = "LEASE"; 
     lease_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     lease_hist_ontime = paymop1_24;
     lease_hist_3060 = pay3059_24;
     lease_hist_6090 = pay6089_24;
     lease_hist_90120 = pay90119_24;
     lease_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then lease_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge &nn2. then do;
	      if acctbal eq 0 then lease_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(lease);
	end;
     end;
  end;


/************************STOP ADDED CODE 5/2019*****************/


  * total auto_loans;
  if loantypt in('AU','AL','AR','AT','CA','MB','MT','RV') then do;
     loantype = "AUTO";
     auto_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline03.sas*/
     auto_hist_ontime = paymop1_&nn.;
     auto_hist_3060 = pay3059_&nn.;
     auto_hist_6090 = pay6089_&nn.;
     auto_hist_90120 = pay90119_&nn.;
     auto_hist_120plus	= pay120up_&nn.;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then auto_clo = 1;


     if cll ne 1 and acctbal ge 0 and dtveri ge &nn2. then do;
      /*  if acctbal eq 0 then auto_clo; */
       if acctbal eq 0 then auto_clo = 1;
        if acctbal gt 0 then do;
	   %recentcount(auto);
	end; 
     end;
  end;

  
  * total student debt;
  if loantypt ='ST' then do;
     loantype = "STUD";
     student_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline03.sas*/
     student_hist_ontime = paymop1_&nn.;
     student_hist_3060 = pay3059_&nn.;
     student_hist_6090 = pay6089_&nn.;
     student_hist_90120 = pay90119_&nn.;
     student_hist_120plus = pay120up_&nn.;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then student_clo = 1;
	
	   if cll ne 1 and acctbal ge 0 and dtveri ge &nn2. then do;
        if acctbal eq 0 then student_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(student);
     	end;   
     end;
  end;

  /* total mortgage debt*/
  if loantypt in ('CV', 'FH', 'FI', 'FR', 'HE', 'HI', 'PI', 'RE', 'RL', 'RM', 'RT', 'SM', 'VA', 'VM','UK',' ') and acctype eq 'M' then do;
     loantype = "MORT"; 
     mortgage_open = 1;

     mortgage_hist_ontime = paymop1_&nn.;
     mortgage_hist_3060 = pay3059_&nn.;
     mortgage_hist_6090 = pay6089_&nn.;
     mortgage_hist_90120 = pay90119_&nn.;
     mortgage_hist_120plus = pay120up_&nn.;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then mortgage_clo = 1;
	
     if cll ne 1 and acctbal ge 0 and dtveri ge &nn2. then do;
        if acctbal eq 0 then mortgage_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(mortgage);
     	end;
     end;
  end;

  /* credit card debt (secured)*/
  /* We want to include accounts with zero balances so that we can construct an accurate measure of how many
  people have no credit cards*/
  if loantypt in('CC') then do;
     loantype = "CCAR";
     
     if ecoa = "I" then do;
      ccard_indiv_open = 1;
     	ccard_indiv_hist_ontime = paymop1_&nn.;
	    ccard_indiv_hist_3060 = pay3059_&nn.;
     	ccard_indiv_hist_6090 = pay6089_&nn.;
     	ccard_indiv_hist_90120 = pay90119_&nn.;
     	ccard_indiv_hist_120plus = pay120up_&nn.;
     end;

     if ecoa = "C" then do;
     	ccard_joint_open = 1;
	    ccard_joint_hist_ontime = paymop1_&nn.;
	    ccard_joint_hist_3060 = pay3059_&nn.;
     	ccard_joint_hist_6090 = pay6089_&nn.;
     	ccard_joint_hist_90120 = pay90119_&nn.;
     	ccard_joint_hist_120plus = pay120up_&nn.;
     end;

     if ecoa in ("M", "S", "C") then do;
     	ccard_cosign_open = 1;
	    ccard_cosign_hist_ontime = paymop1_&nn.;
	    ccard_cosign_hist_3060 = pay3059_&nn.;
     	ccard_cosign_hist_6090 = pay6089_&nn.;
     	ccard_cosign_hist_90120 = pay90119_&nn.;
     	ccard_cosign_hist_120plus = pay120up_&nn.;
     end;

     if ecoa = "A" then do;
     	ccard_notliab_open = 1;
	    ccard_notliab_hist_ontime = paymop1_&nn.;
	    ccard_notliab_hist_3060 = pay3059_&nn.;
     	ccard_notliab_hist_6090 = pay6089_&nn.;
     	ccard_notliab_hist_90120 = pay90119_&nn.;
     	ccard_notliab_hist_120plus = pay120up_&nn.;
     end;

     if ecoa in ("A", "P") then do;
     	ccard_maybeliab_open = 1;
	    ccard_maybeliab_hist_ontime = paymop1_&nn.;
	    ccard_maybeliab_hist_3060 = pay3059_&nn.;
     	ccard_maybeliab_hist_6090 = pay6089_&nn.;
     	ccard_maybeliab_hist_90120 = pay90119_&nn.;
     	ccard_maybeliab_hist_120plus = pay120up_&nn.;
     end;

     * individual account;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa = "I" then ccard_indiv_clo = 1;
	
     /*open account balance, delinquency info*/
     if cll ne 1 and acctbal ge 0 and dtveri ge &nn2. and ecoa = "I" then do;
     	%recentcount(ccard_indiv)
     end;

     * joint account;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa = "C" then ccard_joint_clo = 1;
	     
    /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge &nn2. and ecoa = "C" then do;
 	%recentcount(ccard_joint);
     end;
    
     * cosigner;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("M", "S", "C") then ccard_cosign_clo = 1;
	 
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge &nn2. and ecoa in ("M", "S", "C") then do;
        %recentcount(ccard_cosign);
     end; 

     * not liable;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A") then ccard_notliab_clo = 1;
	
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge &nn2. and ecoa in ("A") then do;
        %recentcount(ccard_notliab);
     end; 

     * maybe liable;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A", "P") then ccard_maybeliab_clo = 1;
	
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge &nn2. and ecoa in ("A", "P") then do;
        %recentcount(ccard_maybeliab); 
     end; 

  end;

  /* credit card total debt (secured + unsecured)*/
  /* We want to include accounts with zero balances so that we can construct an accurate measure of how many
  people have no credit cards*/
  if loantypt in('CC','SC') then do;

     if ecoa = "I" then do;
     	cctot_indiv_open = 1;
     	cctot_indiv_hist_ontime = paymop1_&nn.;
	    cctot_indiv_hist_3060 = pay3059_&nn.;
     	cctot_indiv_hist_6090 = pay6089_&nn.;
     	cctot_indiv_hist_90120 = pay90119_&nn.;
     	cctot_indiv_hist_120plus = pay120up_&nn.;
     end;

     if ecoa = "C" then do;
      cctot_joint_open = 1;
	    cctot_joint_hist_ontime = paymop1_&nn.;
	    cctot_joint_hist_3060 = pay3059_&nn.;
     	cctot_joint_hist_6090 = pay6089_&nn.;
     	cctot_joint_hist_90120 = pay90119_&nn.;
     	cctot_joint_hist_120plus = pay120up_&nn.;
     end;

     if ecoa in ("M", "S", "C") then do;
     	cctot_cosign_open = 1;
	    cctot_cosign_hist_ontime = paymop1_&nn.;
	    cctot_cosign_hist_3060 = pay3059_&nn.;
     	cctot_cosign_hist_6090 = pay6089_&nn.;
     	cctot_cosign_hist_90120 = pay90119_&nn.;
     	cctot_cosign_hist_120plus = pay120up_&nn.;
     end;

     if ecoa = "A" then do;
     	cctot_notliab_open = 1;
	    cctot_notliab_hist_ontime = paymop1_&nn.;
	    cctot_notliab_hist_3060 = pay3059_&nn.;
     	cctot_notliab_hist_6090 = pay6089_&nn.;
     	cctot_notliab_hist_90120 = pay90119_&nn.;
     	cctot_notliab_hist_120plus = pay120up_&nn.;
     end;

     if ecoa in ("A", "P") then do;
     	cctot_maybeliab_open = 1;
	    cctot_maybeliab_hist_ontime = paymop1_&nn.;
	    cctot_maybeliab_hist_3060 = pay3059_&nn.;
     	cctot_maybeliab_hist_6090 = pay6089_&nn.;
     	cctot_maybeliab_hist_90120 = pay90119_&nn.;
     	cctot_maybeliab_hist_120plus = pay120up_&nn.;
     end;

     * individual account;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa = "I" then cctot_indiv_clo = 1;
	  
     /*open account balance, delinquency info*/
     if cll ne 1 and acctbal ge 0 and dtveri ge &nn2. and ecoa = "I" then do;
     	%recentcount(cctot_indiv);
     end;

     * joint account;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa = "C" then cctot_joint_clo = 1;
	 
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge &nn2. and ecoa = "C" then do;
	%recentcount(cctot_joint);
     end;
    
     * cosigner;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("M", "S", "C") then cctot_cosign_clo = 1;
	
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge &nn2. and ecoa in ("M", "S", "C") then do;
        %recentcount(cctot_cosign);
     end; 

     * not liable;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A") then cctot_notliab_clo = 1;
	
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge &nn2. and ecoa in ("A") then do;
        %recentcount(cctot_notliab); 
     end; 

     * maybe liable;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A", "P") then cctot_maybeliab_clo = 1;
	
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge &nn2. and ecoa in ("A", "P") then do;
        %recentcount(cctot_maybeliab);
     end; 
  end;

run;

proc sort data = temp;
  by permid;
run;

proc print data=temp (obs=10);
run;
proc contents data=temp;
run;

/*select variables that we want to get sum for collapsed dataset*/
proc sql;
     select name
     into :sumlist separated by ' '
     from dictionary.columns
     where libname = 'WORK' and memname = 'TEMP'
     and (name ? '_debt' or name ? '_num' or name like '%_ontime' 
     or name like '_lim' or name like '%3060' or name like '%6090' or name like '%90120' 
     or name like '%120plus' or name like '%_worse'
     or name ? '_clo' or name ? '_open');
     quit;
/*select lot variables that we want to get min (choose earliest date)*/
proc sql;
     select name 
     into :minlist separated by ' '
     from dictionary.columns
     where libname = 'WORK' and memname = 'TEMP'
     and (name ? '_lot' or name ? '_f120');
     quit;
/*collapse the dataset*/
 proc means data = temp noprint;
  by permid;
  var install: lcred: unsec: secgoods: lease: auto: trans: student: mortgage: ccard: cctot:;
  output out=rev_install_type&sufx. min(&minlist)= sum(&sumlist)=;
run;

data rev_install_type&sufx.;
    set rev_install_type&sufx.;
    drop _type_ _freq_;

    ccard_indiv_joint_debt=sum(ccard_indiv_debt,.5*ccard_cosign_debt);
    cctot_indiv_joint_debt=sum(cctot_indiv_debt,.5*cctot_cosign_debt);

ccard_indiv_joint_lim=sum(ccard_indiv_lim,.5*ccard_cosign_lim);
    cctot_indiv_joint_lim=sum(cctot_indiv_lim,.5*cctot_cosign_lim);

run;


proc sort data=rev_install_type&sufx.; by permid; run;
proc sort data=temp; by permid; run;


data temp_new;

 merge temp (in = a keep= permid teditseq dtopen dtveri dtclo terms hicredit install_clo install_open lcred_clo lcred_open unsec_clo unsec_open secgoods_clo secgoods_open lease_clo lease_open student_clo student_open mortgage_clo mortgage_open auto_clo auto_open loantype student_debt crdtlim acctbal ecoa
                    rename=(student_debt=student_debt_tl student_clo=student_clo_tl student_open=student_open_tl 
install_clo=install_clo_tl 
install_open = install_open_tl
lcred_clo = lcred_clo_tl
lcred_open = lcred_open_tl 
unsec_clo = unsec_clo_tl 
unsec_open = unsec_open_tl 
secgoods_clo = secgoods_clo_tl
secgoods_open = secgoods_open_tl
lease_clo = lease_clo_tl
lease_open = lease_open_tl 
terms=terms_tl 
hicredit=hicredit_tl 
mortgage_clo=mortgage_clo_tl 
                            mortgage_open=mortgage_open_tl auto_clo=auto_clo_tl auto_open=auto_open_tl crdtlim=crdtlim_tl acctbal=acctbal_tl ecoa=ecoa_tl))

       rev_install_type&sufx. (in = b) ;
by permid ;      
  
if a; 
run;



/*pull roll up variables from SCORE dataset*/ 
/*ON33 G044s** G045s g060 g062s g065s g067s g070s G096 S059** */
/*G082-G086** G087 */
/*CensusBlockGroup08 CensusSuffix08*/
/*CensusTract08 CountyFIPSCode08 Latitude08 Longitude08*/
/*StateFIPSCode08 */

data roll_up&sufx.(drop=AT01S);
     set scorein.tu&sufx._merged (keep=
         permid RE28s RE34s AT33a AT28a  MT33s IN33s
         RE33s at01s G041s G042s G043s G057s
         g058s g059s g061s g063s g064s g066s g068s g069s g071s
         ZIPCODE STATE
         G104s G093s G094s  S063a S064a G106s
         hmop5gth derogpub cmop2 cmop3 cmop4 cmop5
         CBSA State_FIPS_Code score
         County_FIPS_Code Cenus_Tract Cenus_Suffix
         Census_Block_Group Latitude Longitude





         rename=(STATE=STATEchar_10 ZIPCODE=ZIP_10));
     rename score=tr_am;
     /*if AT01 ne 0;	 we want to include obs with 0 tradelines from score per conversation
     	      	 	 with alvaro - AN 06.20.13 */
     select (STATEchar_10);
     	    when ('AL') STATE=01;
     	    when ('AK') STATE=02;
     	    when ('AZ') STATE=04;
     	    when ('AR') STATE=05;
     	    when ('CA') STATE=06;
     	    when ('CO') STATE=08;
     	    when ('CT') STATE=09;
     	    when ('DE') STATE=10;
     	    when ('DC') STATE=11;
     	    when ('FL') STATE=12;
     	    when ('GA') STATE=13;
     	    when ('HI') STATE=15;
     	    when ('ID') STATE=16;
     	    when ('IL') STATE=17;
     	    when ('IN') STATE=18;
     	    when ('IA') STATE=19;
     	    when ('KS') STATE=20;
     	    when ('KY') STATE=21;
     	    when ('LA') STATE=22;
     	    when ('ME') STATE=23;
     	    when ('MD') STATE=24;
     	    when ('MA') STATE=25;
     	    when ('MI') STATE=26;
     	    when ('MN') STATE=27;
     	    when ('MS') STATE=28;
     	    when ('MO') STATE=29;
     	    when ('MT') STATE=30;
     	    when ('NE') STATE=31;
     	    when ('NV') STATE=32;
     	    when ('NH') STATE=33;
     	    when ('NJ') STATE=34;
     	    when ('NM') STATE=35;
     	    when ('NY') STATE=36;
     	    when ('NC') STATE=37;
     	    when ('ND') STATE=38;
     	    when ('OH') STATE=39;
     	    when ('OK') STATE=40;
     	    when ('OR') STATE=41;
     	    when ('PA') STATE=42;
     	    when ('PR') STATE=43;
     	    when ('RI') STATE=44;
     	    when ('SC') STATE=45;
     	    when ('SD') STATE=46;
     	    when ('TN') STATE=47;
     	    when ('TX') STATE=48;
     	    when ('UT') STATE=49;
     	    when ('VT') STATE=50;
     	    when ('VA') STATE=51;
     	    when ('WA') STATE=53;
     	    when ('WV') STATE=54;
     	    when ('WI') STATE=55;
     	    when ('WY') STATE=56;
     	    when ('VI') STATE=78;
     	    when ('AS') STATE=60;
     	    when ('GU') STATE=66;
	    when ('')   STATE=.;
	    otherwise STATE=0;
     end;
run;

proc sort data = roll_up&sufx.;
  by permid;
run;

proc contents data=roll_up&sufx.;
run;

proc print data=roll_up&sufx.(obs=20);
run;

data in.roll_trades&sufx. (rename=(teditseq=teditseq10));
     merge roll_up&sufx. 
           temp_new ;
     by permid;  /*merge must be unconditional on in=a or in=b*/
run;


data in.roll_trades&sufx.;
     retain permid MT33S mortgage_debt IN33s RE33s;
     set in.roll_trades&sufx.;
run;

proc print data=in.roll_trades&sufx.(obs=1000);
run;

proc freq data=in.roll_trades&sufx.;
tables mortgage_debt;
run;
%mend;



%let nn2=200812;
%let nn=18;
%let sufx=201006;
%runme;
run;

%let nn2=201006;
%let nn=24;
%let sufx=201206;
%runme;
run;


%let nn2=201206;
%let nn=24;
%let sufx=201406;
%runme;
run;
